# -*- coding: utf-8 -*-
"""
Created on Thu Feb 06 17:49:09 2025

@author: wangwenjie
"""

import numpy as np
import pandas as pd
import datetime
import pymysql
import os
from sqlalchemy import create_engine
import seaborn as sns
import matplotlib as mpl
import matplotlib.pyplot as plt
import matplotlib.pyplot as plt
plt.style.use('ggplot')
mpl.rcParams['font.sans-serif'] = [u'SimHei']  # 指定默认字体
plt.rcParams['font.sans-serif'] = ["SimHei"]
mpl.rcParams['legend.frameon'] = 'False'

# 参数设置
# 提取数据范围
str_date = '2024-12-27'
end_date = '2025-06-17' # 更新截止日期
# 常规统计日期截点
date1 = '2025-06-13' # 上周最后一个交易日
date2 = '2025-06-06' # 时间设定1
date3 = '2025-05-30' # 时间设定2
date4 = '2024-12-27' # 时间设定3

# 跟踪列表 备注：顺序很重要，要和excel一致
dict = {'SXE204A': '合晟均安11号',
        'SVE640': '富涌谷溪海1号',
        'SGW861A': '合晟恒嘉2号',
        'S22497': '合晟同晖1号',
        'SW3004': '利位3号',
        'SQJ067A': '守朴进取3号',
        'SEZ246A': '守朴价值1号',
        'STT666A': '守朴价值6号',
        'SQY447': '复熙固收1号',
        'SS9022': '合晟同晖7号',
        'SANZ73A': '盈怀香柏树1号A',
        'STV557': '深高稳进9号',
        'SAQJ32': '东方财富证券昇财1号',
        'SASE14A': '盈怀香柏树11号A',
        'SAUV17A': '盈怀香柏树15号A',
        'SQS518': '守朴进取6号',
        'SZR941': '岚湖丰泽2号',
        'SX9850': '复熙恒赢11号',
        'SB3954B': '银叶永泰宏观1号B类',
        'SY0842': '利位稳健1号',
        'STV498': '宁水国债宝5号(稳健)',
        'SJT536': '宁水国债宝2号(专业)',
        'SXD799': '力驶皋乐3号',
        'STG126': '稳进5号(弹性)',
        'STL592': '稳进9号(稳健)',
        'SZF635': '佳岳国债增强',
        'SZK576': '禅龙信望1号',

        'SAGZ50A': '聚联汇破晓1号A',
        'SVU714': '本利达利磐十二',
        'SQN204B': '具力芒种2号B',
        'STE836B': '具力芒种1号B',
        'SVH952': '汉盛晓希1号',
        'SXA627': '汉盛新千禧1号',
        'SZX167': '汉鸿木盛转债增强1号',
        'SXD310': '力驶如屹量化',
        'SX5536A': '艾方多策略4号A',
        'SXL302': '哈希转债对冲A',
        'SANZ78A': '盈怀问荆1号A',
        'SQK547': '富涌谷转债1号',
        'SACF54': '汉鸿青云1号',
        'SNJ558': '翊安投资转债8号1期',

        'SAPT31B': '宁水灵泰多策略1号B类'}

# 提取数据
def get_data(dict):  # 获取净值
    conf = {'username': 'hs_wangwenjie',
            'password': 'hs_wangwenjie#A0',
            'host': '192.168.201.187',
            'port': 3306,
            'db': 'touyan'}
    url = 'mysql+pymysql://%s:%s@%s:%s/%s' % (
    conf['username'], conf['password'], conf['host'], conf['port'], conf['db'])
    engine = create_engine(url)
    query = """SELECT a.t_date, b.c_reg_code, a.c_fund_id, a.c_fund_name, a.n_nav, a.n_added_nav, a.n_swanav
                FROM self_fund_nv_d AS a
                LEFT JOIN self_fund_info AS b 
                ON a.c_fund_id = b.c_fund_id
                WHERE c_reg_code IN {}
                AND t_date >= '{}'
                ORDER BY c_reg_code, t_date DESC""".format(tuple(dict.keys()), str_date)
    df = pd.read_sql_query(query, engine)
    return df

# 统计收益
df_nav = get_data(dict)
df_nav = df_nav.pivot('t_date','c_reg_code','n_swanav')
df_nav.index = pd.to_datetime(df_nav.index)
df_performance = pd.DataFrame(index=dict.keys())
df_performance['{}-{}'.format(date1, end_date)] = pd.DataFrame(df_nav.loc[pd.Timestamp(end_date),:] / df_nav.loc[pd.Timestamp(date1),:] - 1) # 当周收益
df_performance['{}-{}'.format(date2, date1)] = pd.DataFrame(df_nav.loc[pd.Timestamp(date1),:] / df_nav.loc[pd.Timestamp(date2),:] - 1)
df_performance['{}-{}'.format(date3, date1)] = pd.DataFrame(df_nav.loc[pd.Timestamp(date1),:] / df_nav.loc[pd.Timestamp(date3),:] - 1)
df_performance['{}-{}'.format(date4, date1)] = pd.DataFrame(df_nav.loc[pd.Timestamp(date1),:] / df_nav.loc[pd.Timestamp(date4),:] - 1)
df_performance.index = dict.values()

# 生成日收益数据
df_nav_chg = df_nav.pct_change()
df_nav_chg = df_nav_chg.sort_index(ascending=False).T
df_nav_chg = df_nav_chg.reindex(dict.keys())
df_nav_chg.index = dict.values()

# 导出数据
path = os.getcwd()
new_dir = os.path.join(path, 'output')
if not os.path.exists(new_dir):
        os.makedirs(new_dir)
wb = pd.ExcelWriter(os.path.join(new_dir, "核心池收益跟踪.xlsx"))
df_performance.to_excel(wb, '核心池收益跟踪', index=True)
df_nav_chg.to_excel(wb, '日收益跟踪', index=True)
df_nav.to_excel(wb, '核心池净值跟踪', index=True)
wb.save()